package com.dap.utils;

import com.dap.commons.Constants;
import com.dap.exception.BusinessException;
import com.dap.oss.UploadService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.*;

/**
 * Created by wangmin on 2017/6/15.
 */
public abstract class ExcelUtils {
    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    private static Object getFormulaCellValue(Cell cell) {
        switch (cell.getCachedFormulaResultTypeEnum()) {
            case NUMERIC:
                return new BigDecimal(cell.getNumericCellValue());
            case BOOLEAN:
                return cell.getBooleanCellValue();
        }
        return null;
    }
    /**
     * 根据HSSFCell类型获取数据
     * @param cell
     * @return
     */
    private static Object getCellFormatValue(Cell cell) {
        if (null == cell)
            return null;

        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
                // 如果是纯数字
                else {
                    return new BigDecimal(cell.getNumericCellValue());
                }

            case FORMULA:
                return getFormulaCellValue(cell);

            case STRING:
                return cell.getStringCellValue();

            case BOOLEAN:
                return cell.getBooleanCellValue();

            case BLANK:
                return "";

            // 默认的Cell值
            default:
                return null;
        }
    }

    private static Workbook createWorkbookFromInputStream(InputStream is, String extension) throws IOException {
        Workbook wb;
        try {
            if (Constants.EXCEL_EXTENSION_XLS.equalsIgnoreCase(extension)) {
                wb = new HSSFWorkbook(is);
            } else if (Constants.EXCEL_EXTENSION_XLSX.equalsIgnoreCase(extension)) {
                wb = new XSSFWorkbook(is);
            } else {
                throw new BusinessException(Constants.ResponseCode.EXCEL_INVALID_EXTENSION);
            }
        } catch (BusinessException e) {
            throw e;
        } catch (Exception e) {
            logger.warn("", e);
            throw new BusinessException(Constants.ResponseCode.EXCEL_INVALID_CONTENT);
        } finally {
            is.close();
        }
        return wb;
    }

    private static Object[][][] readWorkbookBasicData(Workbook wb, int readSheets,
            int startRowIndex, int startColIndex) {
        Object[][][] sheetDataArray = new Object[readSheets][][];

        for (int sheetIdx=0; sheetIdx<readSheets; ++sheetIdx) {
            Sheet sheet = wb.getSheetAt(sheetIdx);
            int rowCount = sheet.getLastRowNum() + 1;   // 得到总行数
            Object[][] sheetData = sheetDataArray[sheetIdx] = new Object[rowCount-startRowIndex][];

            for (int i = startRowIndex; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (null == row)
                    continue;

                int colCount = row.getLastCellNum() + 1;
                Object[] rowData = new Object[colCount - startColIndex];
                sheetData[i - startRowIndex] = rowData;

                for (int j = startColIndex; j < colCount; ++j) {
                    rowData[j - startColIndex] = getCellFormatValue(row.getCell((short) j));
                }
            }
        }

        return sheetDataArray;
    }

    private static class MyPictureData {
        public final PictureData pictureData;
        public final int sheetIdx;
        public final int rowIdx;
        public final int colIdx;
        public MyPictureData(PictureData pictureData, int sheetIdx, int rowIdx, int colIdx) {
            this.pictureData = pictureData;
            this.sheetIdx = sheetIdx;
            this.rowIdx = rowIdx;
            this.colIdx = colIdx;
        }
    }
    private static List<MyPictureData> getPicturesOfHSSFSheet(HSSFSheet sheet, final int sheetIndex,
            int startRowIndex, int startColIndex) {
        List<MyPictureData> resultList = new ArrayList<>();

        if (null == sheet.getDrawingPatriarch()) {
            return resultList;
        }
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();

        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                resultList.add(new MyPictureData(pdata, sheetIndex, cAnchor.getRow1()-startRowIndex, cAnchor.getCol1()-startColIndex));
            }
        }
        return resultList;
    }
    private static List<MyPictureData> getPicturesOfXSSFSheet(XSSFSheet sheet, final int sheetIndex,
            int startRowIndex, int startColIndex) {
        List<MyPictureData> resultList = new ArrayList<>();

        List<POIXMLDocumentPart> list = sheet.getRelations();
        if (null == list) {
            return resultList;
        }

        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    resultList.add(new MyPictureData(picture.getPictureData(), sheetIndex, marker.getRow()-startRowIndex, marker.getCol()-startColIndex));
                }
            }
        }
        return resultList;
    }
    private static List<MyPictureData> readWorkbookPicture(Workbook wb, String extension, int readSheets,
            int startRowIndex, int startColIndex) {
        List<MyPictureData> all = new ArrayList<>();

        if (Constants.EXCEL_EXTENSION_XLS.equalsIgnoreCase(extension)) {
            for (int sheetIndex=0; sheetIndex<readSheets; ++sheetIndex) {
                List<MyPictureData> list =
                        getPicturesOfHSSFSheet((HSSFSheet) wb.getSheetAt(sheetIndex), sheetIndex, startRowIndex, startColIndex);
                all.addAll(list);
            }
        } else if(Constants.EXCEL_EXTENSION_XLSX.equalsIgnoreCase(extension)) {
            for (int sheetIndex=0; sheetIndex<readSheets; ++sheetIndex) {
                List<MyPictureData> list =
                        getPicturesOfXSSFSheet((XSSFSheet) wb.getSheetAt(sheetIndex), sheetIndex, startRowIndex, startColIndex);
                all.addAll(list);
            }
        }

        return all;
    }



    public static Object[][] readSingleSheetExcelContent(
            InputStream is,
            final int startRowIndex, final int startColIndex,
            String extension) throws Exception {
        Object[][][] sheetDataArray = readMultiSheetExcelContent(is, 1, startRowIndex, startColIndex, extension);
        return sheetDataArray[0];
    }
    public static Object[][][] readMultiSheetExcelContent(
            InputStream is,
            int maxReadSheetCount,
            final int startRowIndex, final int startColIndex,
            String extension) throws Exception {
        Workbook wb;
        wb = createWorkbookFromInputStream(is, extension);

        // <=0 或者超过最大sheet数量, 默认处理所有sheet
        if (maxReadSheetCount <= 0 || maxReadSheetCount > wb.getNumberOfSheets()) {
            maxReadSheetCount = wb.getNumberOfSheets();
        }

        Object[][][] sheetDataArray = readWorkbookBasicData(wb, maxReadSheetCount, startRowIndex, startColIndex);

        wb.close();

        return sheetDataArray;
    }


    public interface HandleExcelDataAsyncInterface {
        void validateExcelData(Object content, Object otherParams);
        String uploadTempFile(String fileName, byte[] bytes);
        Object importExcelData(Object content, Object otherParams);
        String removeUploadedTempFile(List<String> fileNames);
    }
    private static final ExecutorService FIXED_THREAD_POOL =
            new ThreadPoolExecutor(5, 8, 1L, TimeUnit.SECONDS, new LinkedBlockingQueue<Runnable>(30));
    private static final int pictureMaxSize = 5<<20;
    private static final String pictureMaxSizeName = "5M";
    public static void handleExcelAsync(
            InputStream is, final String extension,
            final int startRowIndex, final int startColIndex,
            final HandleExcelDataAsyncInterface handleInterface, final Object otherParams) throws Exception {
        final Workbook wb = createWorkbookFromInputStream(is, extension);

        if (wb.getNumberOfSheets() <= 0) {
            throw new BusinessException(Constants.ResponseCode.EXCEL_INVALID_CONTENT);
        }

        // 读取excel内容
        final Object[][][] sheetDataArray;
        final List<MyPictureData> pl;
        try {
            final int numberOfSheets = wb.getNumberOfSheets();
            sheetDataArray = readWorkbookBasicData(wb, numberOfSheets, startRowIndex, startColIndex);
            if (isArrayEmpty(sheetDataArray)) {
                throw new BusinessException(Constants.ResponseCode.EXCEL_NO_DATA);
            }
            pl = readWorkbookPicture(wb, extension, numberOfSheets, startRowIndex, startColIndex);
        } finally {
            // 关闭excel
            wb.close();
        }

        // 验证图片
        validatePictureList(startRowIndex, startColIndex, pl);
        // 验证excel内容
        handleInterface.validateExcelData(sheetDataArray, otherParams);


        FIXED_THREAD_POOL.execute(new Runnable() {
            public void run() {
                try {
                    handleAsyncInternal(sheetDataArray, pl, handleInterface, otherParams);
                } catch (Exception e) {
                    logger.warn("", e);
                }
            }
        });
    }
    private static void validatePictureList(int startRowIndex, int startColIndex, List<MyPictureData> pl) {
        for (MyPictureData mpd : pl) {
            if (mpd.rowIdx < 0 || mpd.colIdx < 0) {
                String cellName = generateCellName(startRowIndex+mpd.rowIdx, startColIndex+mpd.colIdx);
                throw new BusinessException(Constants.ResponseCode.EXCEL_PICTURE_INVALID_POS, cellName);
            }

            PictureData pictureData = mpd.pictureData;
            byte[] data = pictureData.getData();
            if (data != null && data.length > pictureMaxSize) {
                String cellName = generateCellName(startRowIndex+mpd.rowIdx, startColIndex+mpd.colIdx);
                throw new BusinessException(Constants.ResponseCode.EXCEL_PICTURE_TOO_LARGE, cellName, pictureMaxSizeName);
            }
        }
    }
    private static void handleAsyncInternal(Object[][][] sheetDataArray, List<MyPictureData> pl,
            HandleExcelDataAsyncInterface handleInterface, Object otherParams) throws Exception {
        // 上传图片至oss并替换内容为oss链接
        final String filePathPrefix = UploadService.tempFilesPathPrefix + DateUtil.format(new Date(), "yyyyMMdd") + "/";
        final List<String> fileNames = new ArrayList<>();
        for (MyPictureData mpd : pl) {
            final String fileName = filePathPrefix + UUID.randomUUID() + "." + mpd.pictureData.suggestFileExtension();
            fileNames.add(fileName);

            final String url = handleInterface.uploadTempFile(fileName, mpd.pictureData.getData());
            setElementOfArray(sheetDataArray, url, mpd.sheetIdx, mpd.rowIdx, mpd.colIdx);
        }

        // 处理业务数据
        try {
            handleInterface.importExcelData(sheetDataArray, otherParams);
        } catch (Exception e) {
            logger.info("", e);
        }

        // 删除上传文件
        if (!fileNames.isEmpty()) {
            handleInterface.removeUploadedTempFile(fileNames);
        }
    }




    private static Cell createAndSetCellFormatValue(Row row, short colIdx, Object data) {
        Cell cell;

        if (null == data) {
            cell = row.createCell(colIdx);
        } else if (data instanceof String) {
            cell = row.createCell(colIdx, CellType.STRING);
            cell.setCellValue((String) data);
        } else if (data instanceof Date) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue((Date) data);
        } else if (data instanceof Integer) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue((Integer) data);
        } else if (data instanceof Long) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue((Long) data);
        } else if (data instanceof Double) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue((Double) data);
        } else if (data instanceof Float) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue((Float) data);
        } else if (data instanceof BigDecimal) {
            cell = row.createCell(colIdx, CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) data).doubleValue());
        } else if (data instanceof Boolean) {
            cell = row.createCell(colIdx, CellType.BOOLEAN);
            cell.setCellValue((Boolean) data);
        } else {
            cell = row.createCell(colIdx);
        }

        return cell;
    }
    private static void setCellFormatValue(Cell cell, Object data) {
        if (null == data)
            return;

        if (data instanceof String) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue((String) data);
        } else if (data instanceof Date) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Date) data);
        } else if (data instanceof Integer) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Integer) data);
        } else if (data instanceof Long) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Long) data);
        } else if (data instanceof Double) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Double) data);
        } else if (data instanceof Float) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue((Float) data);
        } else if (data instanceof BigDecimal) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) data).doubleValue());
        } else if (data instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) data);
        }
    }


    private static Workbook resolveWorkBook(String modelFilePath) throws IOException, InvalidFormatException {
        Workbook wb = null;
        String extension = modelFilePath.substring(modelFilePath.lastIndexOf('.'));
        if (Constants.EXCEL_EXTENSION_XLS.equalsIgnoreCase(extension)) {
            POIFSFileSystem fs = new POIFSFileSystem(new File(modelFilePath));
            wb = new HSSFWorkbook(fs);
        } else if(Constants.EXCEL_EXTENSION_XLSX.equalsIgnoreCase(extension)) {
            wb = new XSSFWorkbook(new FileInputStream(new File(modelFilePath)));
        }
        return wb;
    }
    public static void writeExcel(
            String modelFilePath,
            Object excelData,
            final int startRowIndex, final int startColIndex,
            OutputStream outputStream) throws Exception {
        Workbook wb = resolveWorkBook(modelFilePath);

        Object[][][] sheetDataArray;
        if (excelData instanceof Object[][][]) { // 三维数组, 多个sheet, 三维数组的判断必须写在二维数组判断的前面
            sheetDataArray = (Object[][][]) excelData;
        } else if (excelData instanceof Object[][]) {  // 只有一个sheet
            sheetDataArray = new Object[1][][];
            sheetDataArray[0] = (Object[][]) excelData;
        } else {
            if (null == excelData)
                throw new Exception("excelData is null");
            else
                throw new Exception("excelData's class is " + excelData.getClass().getName());
        }

        for (int sheetIdx=0; sheetIdx<sheetDataArray.length; ++sheetIdx) {
            Object[][] sheetData = sheetDataArray[sheetIdx];
            if (null == sheetData || sheetData.length == 0)
                continue;
            Sheet sheet = wb.getSheetAt(sheetIdx);

            Map<Integer, Row> rowMap = new HashMap<>();
            for (Iterator<Row> iter = sheet.rowIterator(); iter.hasNext();) {
                Row row = iter.next();
                if (null != row)
                    rowMap.put(row.getRowNum(), row);
            }

            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int rowDataIdx = 0; rowDataIdx < sheetData.length; ++rowDataIdx) {
                final int rowIdx = startRowIndex + rowDataIdx;
                Row row = rowMap.get(rowIdx);
                if (null == row)
                    row = sheet.createRow(rowIdx);
                Object[] rowData = sheetData[rowDataIdx];

                for (int colDataIdx = 0; colDataIdx < rowData.length; ++colDataIdx) {
                    final short colIdx = (short) (startColIndex + colDataIdx);
                    Cell cell = null;
                    if (row.getFirstCellNum() <= colIdx && colIdx <= row.getLastCellNum())
                        cell = row.getCell(colIdx);
                    if (null == cell)
                        cell = createAndSetCellFormatValue(row, colIdx, rowData[colDataIdx]);
                    else
                        setCellFormatValue(cell, rowData[colDataIdx]);
                    // 自动换行
                    cell.getCellStyle().setWrapText(true);
                }
            }
        }

        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
        wb.close();
    }


    private static String int2str26(int v) {
        if (0 == v) {
            return "A";
        }

        StringBuffer sb = new StringBuffer();
        while (v > 0) {
            int t = v % 26;
            sb.append((char)('A'+t));
            v = v / 26;
        }
        return sb.reverse().toString();
    }
    /**
     * 获取单元格名称
     * */
    public static String generateCellName(int rowIdx, int colIdx) {
        final String colName = int2str26(colIdx);
        final String rowName = String.valueOf(rowIdx+1);
        return colName+rowName;
    }


    /**
     * 判断三维数组是否为空(空字符串也为空)
     * */
    public static boolean isArrayEmpty(Object[][][] a3) {
        if (null == a3) {
            return true;
        }
        for (int i=0; i<a3.length; ++i) {
            Object[][] a2 = a3[i];
            for (int j=0; j<a2.length; ++j) {
                Object[] a1 = a2[j];
                for (int k=0; k<a1.length; ++k) {
                    Object a0 = a1[k];
                    if (null != a0) {
                        if (a0 instanceof String) {
                            if (!StringUtils.isEmpty((String)a0)) {
                                return false;
                            }
                        } else {
                            return false;
                        }
                    }
                }
            }
        }
        return true;
    }
    /**
     * 设置三维数组指定元素
     * */
    public static boolean setElementOfArray(Object[][][] array, Object o, int idx0, int idx1, int idx2) {
        if (array.length <= idx0 || array[idx0].length <= idx1) {
            return false;
        }

        // 在最后一个维度, 如果长度不够, 则扩展
        if (array[idx0][idx1].length <= idx2) {
            Object[] newArray = new Object[idx2+1];
            System.arraycopy(array[idx0][idx1], 0, newArray, 0, array[idx0][idx1].length);
            array[idx0][idx1] = newArray;
        }

        array[idx0][idx1][idx2] = o;
        return true;
    }

    /**
     * 去除后面连续的空行, 中间的空行不删除
     * */
    public static Object[][] leaveOutLastEmptyRows(Object[][] data) {
        if (null == data) {
            return new Object[0][];
        }

        for (int rowIdx = data.length - 1; rowIdx >= 0; --rowIdx) {
            Object[] rowData = data[rowIdx];
            if (null == rowData || rowData.length <= 0) {
                continue;
            }

            for (int colIdx = rowData.length - 1; colIdx >= 0; --colIdx) {
                if (null == rowData[colIdx]) {
                    continue;
                }
                if (rowData[colIdx] instanceof String && StringUtils.isBlank((String)rowData[colIdx])) {
                    continue;
                }

                if (null != rowData[colIdx] && (!(rowData[colIdx] instanceof String) || !StringUtils.isBlank((String)rowData[colIdx]))) {
                    if (rowIdx == data.length - 1) {
                        return data;
                    } else {
                        int resultRowCount = rowIdx + 1;
                        Object[][] resultData = new Object[resultRowCount][];

                        for(int i = 0; i < resultRowCount; ++i) {
                            resultData[i] = data[i];
                        }

                        return resultData;
                    }
                }
            }
        }

        return new Object[0][];
    }
    /**
     * 去除所有的空行
     * */
    public static Object[][] leaveOutEmptyRows(Object[][] data) {
        if (null == data) {
            return new Object[0][];
        }

        List<Object[]> dataList = new ArrayList<>();
        for (int rowIdx=0; rowIdx<data.length; ++rowIdx) {
            Object[] rowData = data[rowIdx];
            if (null == rowData || rowData.length <= 0) {
                continue;
            }

            for (int colIdx = rowData.length - 1; colIdx >= 0; --colIdx) {
                if (null == rowData[colIdx]) {
                    continue;
                }
                if (rowData[colIdx] instanceof String && StringUtils.isBlank((String)rowData[colIdx])) {
                    continue;
                }

                dataList.add(rowData);
                break;
            }
        }

        Object[][] result = new Object[dataList.size()][];
        for (int i=0; i<dataList.size(); ++i) {
            result[i] = dataList.get(i);
        }
        return result;
    }

}
